
/*

*2024.05.19. Yuan Zi ******************************************************************
*Get Data for all port-pairs stats, save as port-pairs-data2.dta

     
2. Variable Generated, for each port-port pair	 
traffic
travel time
average boat size
Panama canal indicator

3. Variable Generated, for each port
max boat size that passed

4. Note 
post dummy: pre-PA-expansion 0, post-PA-expansion 1
size and traffic provide two measures: teu, or dwt

*/



cd "..\..\data_intermediate" // New version



use ship_port_to_port.dta,clear 
keep A_port_id D_port_id  *55 ship_id A_date D_date			// New path
tempfile temp
save `temp',replace

u triplist_na, clear // new path

*** merge ship specifics
merge m:1 ship_id using ship_all_variables.dta, keepusing(teu dwt)
drop if _merge==2
drop _merge

		
***merge trip specifics		
merge 1:1 ship_id A_port_id A_date D_port_id D_date using `temp'
drop if _merge==2
drop _merge

	
	    *** group ports that are closed
		rename A_* AA_*
		rename D_* A_*
		merge m:1 A_port_id using port_cluster_name, update replace // new path

		drop if _merge==2
		drop _merge 

		rename A_* D_*
		rename AA_* A_*

		merge m:1 A_port_id using port_cluster_name, update replace // new path

		drop if _merge==2
		drop _merge 

		drop if A_port_name == D_port_name & A_country == D_country	// New version

		g month = month(dofc(A_date))		
		g post=0
		replace post=1 if month>6

		drop if trav==0
		drop if dwt<15800

		***keep only ports with at least 5 ships passing
		egen freq = count(ship_id), by(A_port_name D_port_name *country)
		drop if freq<5  
		drop freq*
		
		* a median travel measure not differentiating pre-post, which shall be used to substitute for cases where the pre-post differences in travel time appear extreme
		*** or where travel time is only available either pre or post
		egen travel_hs10 = median(travel), by(A_port_name D_port_name *country *lat *lon)

		
		
*traffic
rename dwt_usage_55 traffic_dwt
rename teu_usage_55 traffic_teu

*average boat size
rename dwt boat_size_dwt 
rename teu boat_size_teu		

preserve		
*collapse (median) travel_hs (sum) traffic* (mean) boat_size* (count) freq = ship_id (first) travel_hs10, by(A_port_name D_port_name *country *lat *lon post)  // if run this get port-pairs-data3
collapse (mean) travel_hs boat_size* (sum) traffic* (count) freq = ship_id (first) travel_hs10, by(A_port_name D_port_name *country *lat *lon post) //if run this get port-pairs-data2

tempfile temp
save `temp',replace
restore 

*average boat size used a port 
preserve
collapse (max) maxa_boat_size_teu= boat_size_teu  maxa_boat_size_dwt= boat_size_dwt, by(A_port_name A_country)
tempfile temp1
save `temp1',replace
restore 

collapse (max) maxd_boat_size_teu= boat_size_teu  maxd_boat_size_dwt= boat_size_dwt, by(D_port_name D_country)
tempfile temp2
save `temp2',replace

use `temp',clear
merge m:1 A_port_name A_country using  `temp1',nogen  keep(1 3)
merge m:1 D_port_name D_country using  `temp2',nogen keep(1 3)
tempfile temp
save `temp',replace

use `temp1',clear
rename A* D*
rename maxa* maxd2*
merge 1:m D_port_name D_country using  `temp',nogen  keep(2 3)
tempfile temp
save `temp',replace

use `temp2',clear
rename D* A*
rename maxd* maxa2*
merge 1:m A_port_name A_country using  `temp',nogen keep(2 3)

replace maxa_boat_size_teu=maxa2_boat_size_teu if maxa2_boat_size_teu >maxa_boat_size_teu &maxa2_boat_size_teu!=.
replace maxd_boat_size_teu=maxd2_boat_size_teu if maxd2_boat_size_teu >maxd_boat_size_teu &maxd2_boat_size_teu!=.

replace maxa_boat_size_dwt=maxa2_boat_size_dwt if maxa2_boat_size_dwt >maxa_boat_size_dwt &maxa2_boat_size_dwt!=.
replace maxd_boat_size_dwt=maxd2_boat_size_dwt if maxd2_boat_size_dwt >maxd_boat_size_dwt &maxd2_boat_size_dwt!=.
drop maxa2* maxd2*

rename maxa_boat_size* A_port_capacity*
rename maxd_boat_size* D_port_capacity*
save temp,replace

use temp,clear
		*merge m:1 A_port_name A_country using $data/port_id_list_balanced // old path
		merge m:1 A_port_name A_country using port_id_list_balanced_small // new path and data

		keep if _merge==3
		drop _merge
		rename A_* AA_*
		rename D_* A_*
		*merge m:1 A_port_name A_country using $data/port_id_list_balanced // old path
		merge m:1 A_port_name A_country using port_id_list_balanced_small // new path and data 
		keep if _merge==3
		drop _merge
		rename A_* D_* 
		rename AA_* A_*
		*merge m:1 A_acid D_acid using  "$data/XPanama_ids.dta",nogen keep(1 3) // old path
		merge m:1 A_acid D_acid using  XPanama_ids_small.dta,nogen keep(1 3) // new path and data


order A_acid D_acid A_port_name A_country D_port_name D_country A_lon A_lat D_lon D_lat  
label var post "PA expansion dummy"
label var XPanama "Pass PA dummy"


save port-pairs-data2,replace

